<?php
namespace app\admin\controller;

use think\Config;
use think\Controller;
use think\Db;
use think\Log;
use app\common\lib\exception\ApiException;
class Database extends UserAuth{

    /**
     * 备份管理
     * @author 王柯 <wangke.email@foxmail.com>
     * @return \think\response\View
     */
    public function index(){

        return view('index');
    }

    /**
     * 数据库信息备份
     * @author 王柯 <wangke.email@foxmail.com>
     * @return mixed
     */
    public function backup(){
        $db = config('database.database');
        $sql ='SHOW TABLE STATUS FROM '.$db;
        $res = Db::query($sql);

        $count = count($res);
        if(request()->isPost()){
            $time_start_second = time();
            $start_time = round(microtime(),2);

            if(!empty($_POST['table'])){

                $sql  = "\n";
                foreach($_POST['table'] as $key => $table){
                    //备份表结构
                    $result = Db::query("SHOW CREATE TABLE `{$table}`");
                    $sql .= "-- -----------------------------\n";
                    $sql .= "-- Table structure for `{$table}`\n";
                    $sql .= "-- -----------------------------\n";
                    $sql .= "SET FOREIGN_KEY_CHECKS=0;\n";
                    $sql .= "DROP TABLE IF EXISTS `{$table}`;\n";
                    $sql .= trim($result[0]['Create Table']) . ";\n";

                    //写入数据注释
                    $sql .= "-- -----------------------------\n";
                    $sql .= "-- Records of `{$table}`\n";
                    $sql .= "-- -----------------------------\n";

                    $select = Db::table($table)->select();

                    //备份表数据
                    foreach($select as $k => $v){
                        $sql .= "INSERT INTO `{$table}` VALUES ('" . implode("', '", $v) . "');\n";
                    }

                }

                $path = ROOT_PATH.'public/backup/'.date('Ymd').'/'.$table.'.sql';
                $dir = ROOT_PATH.'public/backup/'.date('Ymd');

                create_dir($dir);

                $write = file_put_contents($path,$sql);

                if($write){
                    $time_end_second = time();
                    $end_time = $time_end_second - $time_start_second + round(microtime(),2);
                    $second = $end_time - $start_time;
                    return show(config('code.success'),"数据备份成功！耗时 $second s");
                }else{
                    return show(config('code.error'),'数据备份失败！');
                }

            }elseif(empty($_POST['table'])){
                $sql  = "\n";

                foreach($res as $key => $val){

                    //备份表结构
                    $result = Db::query("SHOW CREATE TABLE `{$val['Name']}`");
                    $sql .= "-- -----------------------------\n";
                    $sql .= "-- Table structure for `{$val['Name']}`\n";
                    $sql .= "-- -----------------------------\n";
                    $sql .= "SET FOREIGN_KEY_CHECKS=0;\n";
                    $sql .= "DROP TABLE IF EXISTS `{$val['Name']}`;\n";
                    $sql .= trim($result[0]['Create Table']) . ";\n";

                    //写入数据注释
                    $sql .= "-- -----------------------------\n";
                    $sql .= "-- Records of `{$val['Name']}`\n";
                    $sql .= "-- -----------------------------\n";

                    $select = Db::table($val['Name'])->select();

                    //备份表数据
                    foreach($select as $k => $v){
                        $sql .= "INSERT INTO `{$val['Name']}` VALUES ('" . implode("', '", $v) . "');\n";
                    }

                }

                $path = ROOT_PATH.'public/backup/'.date('Ymd').'/'.$db.'.sql';
                $dir = ROOT_PATH.'public/backup/'.date('Ymd');

                create_dir($dir);
                //halt($sql);
                $write = file_put_contents($path,$sql);

                if($write){
                    $time_end_second = time();
                    $end_time = $time_end_second - $time_start_second + round(microtime(),2);
                    $second = $end_time - $start_time;
                    return show(config('code.success'),"数据备份成功！耗时 $second s");
                }else{
                    return show(config('code.error'),'数据备份失败！');
                }

            }
        }

        $this->assign([
            'count' => $count,
            'res' => $res,
        ]);

        return $this->fetch('backup');
    }

    /**
     * 修复数据表
     * @author 王柯 <wangke.email@foxmail.com>
     * @return array|string|\think\response\Json
     */
    public function repair(){
        if(request()->isPost()){
            $data = $_POST;
            if(empty($data)){
                return show(config('code.error'),'提交的数据不能为空！');
            }

            $table = $data['table'];
            $tables = implode('`,`', $table);

            $res = Db::query("REPAIR TABLE `{$tables}`");
            if($res){
                return show(config('code.success'),'数据表修复完成！');
            }

            return show(config('code.error'),'数据表修复失败！');
        }

        if(request()->isGet()){
            $data = $_GET;
            $table = $data['table'];

            $res = Db::query("REPAIR TABLE `{$table}`");
            if($res){
                return show(config('code.success'),'数据表修复完成！');
            }

            return show(config('code.error'),'数据表修复失败！');
        }
    }

    /**
     * 优化数据表
     * @author 王柯 <wangke.email@foxmail.com>
     * @return array|string|\think\response\Json
     */
    public function optimize(){
        if(request()->isPost()){
            $data = $_POST;
            if(empty($data)){
                return show(config('code.error'),'提交的数据不能为空！');
            }

            $table = $data['table'];
            $tables = implode('`,`', $table);

            $res = Db::query("OPTIMIZE TABLE `{$tables}`");
            if($res){
                return show(config('code.success'),'数据表优化完成！');
            }

            return show(config('code.error'),'数据表优化失败！');
        }

        if(request()->isGet()){
            $data = $_GET;
            $table = $data['table'];

            $res = Db::query("OPTIMIZE TABLE `{$table}`");
            if($res){
                return show(config('code.success'),'数据表优化完成！');
            }

            return show(config('code.error'),'数据表优化失败！');
        }
    }

}